package m.toolkit.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import m.framework.exception.SystemException;
import m.framework.utils.DateTime;
import m.framework.utils.StringUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * @author 王永明
 * @date May 13, 2010 6:35:35 PM
 */
public class ExcelUtil {
	private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 创建excel文件 filePaht 文件路径 contents excel内容,contents里面应该是String[] 或List
	 * title 标题
	 */
	public static String createExcel(String filePath, Map<String, List> contents) {
		WriteExcel writeExcel;
		try {
			writeExcel = new WriteExcel(filePath);
		} catch (Exception e) {
			throw new SystemException("写入excel时出错:"+e.getMessage(),e);
		}
		Set<Entry<String, List>> set = contents.entrySet();
		int no = 1;
		for (Entry<String, List> en : set) {
			String sheetName = en.getKey();
			if (StringUtil.isEmpty(sheetName)) {
				sheetName = "sheet" + no++;
			}
			writeExcel.createNewSheet(sheetName);
			for (Object o : en.getValue()) {
				Object[] obs = null;
				if (o instanceof Object[]) {
					obs = (Object[]) o;
				}
				if (o instanceof List) {
					List c = (List) o;
					obs = c.toArray(new Object[c.size()]);
				}
				writeExcel.createNewRow();
				writeExcel.currNumOfColumns = 0;
				writeExcel.setRowValue(obs);
			}
		}
		writeExcel.close();
		log.info("生成excel:" + filePath);
		return filePath;
	}


	public static String createExcel(String filePath, List contents) {
		Map map = new HashMap();
		map.put("sheet1", contents);
		return createExcel(filePath, map);
	}

	public static void createExcel(String filePath, List contents, String subject) {
		Map map = new HashMap();
		map.put(subject, contents);
		createExcel(filePath, map);
	}

	public static List<List> readExcel(String path) throws IOException {
		InputStream stream = null;
		try {
			stream = new FileInputStream(new File(path));
			Workbook wb = new HSSFWorkbook(stream);
			Sheet sheet = wb.getSheetAt(0);
			int rows = sheet.getLastRowNum();
			int cells = sheet.getRow(0).getPhysicalNumberOfCells();
			List<List> rowList = new ArrayList();
			for (int i = 0; i <= rows; i++) {
				Row row = sheet.getRow(i);
				List oneRow = new ArrayList();
				for (int j = 0; j < cells; j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						Object obj = getCellValue(cell,wb);
						if("null".equals(obj)){
							if(cell.getCellType()==Cell.CELL_TYPE_BLANK){
								obj="";
							}
						}
						oneRow.add(obj);
					}else{
						oneRow.add(null);
					}
				}
				rowList.add(oneRow);
			}
			return rowList;
		} finally {
			if (stream != null) {
				stream.close();
			}
		}

	}

	public static Object getCellValue(Cell cell,Workbook wb) {
		if (cell == null) {
			return "";
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
		case Cell.CELL_TYPE_FORMULA:
			FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
			Object v=evaluator.evaluate(cell);
			return v+"";
		case Cell.CELL_TYPE_NUMERIC:
			Date date=cell.getDateCellValue();
			//如果转换为日期是10年以内,就认为是日期
			if(date!=null&&Math.abs(date.getTime()-System.currentTimeMillis())<1000L*60*60*24*365*10){
				return new DateTime(date)+"";
			}
			
			Double d=cell.getNumericCellValue();
			java.text.DecimalFormat formatter = new java.text.DecimalFormat("########");
			if(d.longValue()-d.doubleValue()!=0){
				formatter=new java.text.DecimalFormat("0.00");
			}
			return formatter.format(d);
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		}
		return null;
	}
	

}
